import requests
import time
from datetime import datetime
from quant_researcher.quant.project_tool.localize import DATA_DIR
import pandas as pd
import json
import os
from logger import logger
import os
import traceback
from task_monitor import task_to_db, send_error_to_email


def getusdtinterest():
    file_name="Binance_USDT_borrow_interest_data.xlsx"
    file_path = os.path.join(DATA_DIR, f'borrow_interest')
    os.makedirs(file_path, exist_ok=True)
    file_path = os.path.join(file_path,file_name)
    
    if os.path.exists(file_path):
        df = pd.read_excel(file_path)
        # 获取最后一行数据
        last_row = df.iloc[-1]
        # 获取最后一行的时间
        last_time = last_row['DateTime']
        # print(f"最后一行的时间: {last_time}")
        # 转换时间为时间戳
        starttime = int(datetime.strptime(last_time, '%Y-%m-%d %H:%M:%S').timestamp()) + 1
        # print(starttime)
        headers = {
            "accept": "application/json",
            "CG-API-KEY": "04c8f778c4124f909fcea0df1d0cb992"
        }
        url = f"https://open-api-v3.coinglass.com/api/borrowInterestRate/history?exchange=Binance&symbol=USDT&interval=h6&startTime={starttime}&limit=4500"
        response = requests.get(url, headers=headers)
        # 将响应内容转换为字典
        response_dict = json.loads(response.text)
        # 提取data部分
        res_data = response_dict.get('data', [])
        # print(response.text)
        data = {
            'DateTime': [],
            'Yearly_Rate': [],
            'Daily_Rate': [],
            'Current_Query_Rate': []
        }
        for r in res_data:
            data['DateTime'].append(datetime.fromtimestamp(r["time"]).strftime('%Y-%m-%d %H:%M:%S'))
            data['Yearly_Rate'].append(r["interestRate"] * 365)
            data['Daily_Rate'].append(r["interestRate"])
            data['Current_Query_Rate'].append(r["interestRate"])
        # 将新数据转换为DataFrame
        new_df = pd.DataFrame(data)
        # 合并原数据和新数据
        df = pd.concat([df, new_df], ignore_index=True)
        # 保存回Excel文件
        df.to_excel(file_path, index=False)
        print(f"新数据已成功加入并保存到 {file_path}")
    else:
        headers = {
            "accept": "application/json",
            "CG-API-KEY": "04c8f778c4124f909fcea0df1d0cb992"
        }
        url = f"https://open-api-v3.coinglass.com/api/borrowInterestRate/history?exchange=Binance&symbol=USDT&interval=h6&limit=4500"
        # print(url)
        response = requests.get(url, headers=headers)
        # 将响应内容转换为字典
        response_dict = json.loads(response.text)
        # 提取data部分
        res_data = response_dict.get('data', [])
        # print(response.text)
        data = {
            'DateTime': [],
            'Yearly_Rate': [],
            'Daily_Rate': [],
            'Current_Query_Rate': []
        }
        for r in res_data:
            data['DateTime'].append(datetime.fromtimestamp(r["time"]).strftime('%Y-%m-%d %H:%M:%S'))

            data['Yearly_Rate'].append(r["interestRate"] * 365)
            data['Daily_Rate'].append(r["interestRate"])
            data['Current_Query_Rate'].append(r["interestRate"])
        print("数据写入成功！")
        # 将数据转换为DataFrame
        df = pd.DataFrame(data)
        # 将DataFrame写入Excel文件
        df.to_excel(file_path, index=False)
        
        
if __name__ == "__main__":
    logger.info("开始getusdtinterest")
    task_to_db(os.path.basename(__file__), 'getusdtinterest')
    try:
        getusdtinterest()
    except Exception as e:
        msg = traceback.format_exc()
        logger.info(msg)
        send_error_to_email(script_name=os.path.basename(__file__), func_name="getusdtinterest", message=msg)
        raise e
    task_to_db(os.path.basename(__file__), 'getusdtinterest', 1)
    logger.info("结束getusdtinterest")